from datetime import datetime

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session, Session
from sqlalchemy import Column, DateTime, Integer, MetaData, String, Table, func, INTEGER, DATETIME, select, or_, JSON
from sqlalchemy.dialects.mysql import TINYINT, VARCHAR

from script.format_goods_data import get_product

# 连接到SQLite数据库
db_engine = create_engine(r'sqlite:///E:\电商_workspace\电商_workspace\公司内部网站\backend\a_plus_max\db\a_plus_max.db')
base_metadata = MetaData()

goods = Table(
    'goods', base_metadata,
    Column('id', Integer, primary_key=True),
    Column('goods_number', VARCHAR(20), nullable=False),
    Column('goods_cn_name', VARCHAR(200)),
    Column('goods_en_name', VARCHAR(200)),
    Column('created_at', DateTime, nullable=False, default=func.now()),
    Column('deleted_at', DateTime),
    Column('updated_at', DateTime, default=func.now(), onupdate=func.now()),
    Column('goods_image', VARCHAR(500), nullable=False),
    Column('goods_price', Integer, nullable=False, comment="所有的数都乘1000"),
    Column('factory_name', VARCHAR(200)),
    Column('factory_link', VARCHAR(500), nullable=False),
    Column('goods_carton_size', VARCHAR(50)),
    Column('goods_carton_number', Integer),
    Column('goods_carton_weight', Integer, comment="所有的数都乘1000"),
    Column('goods_size', VARCHAR(50)),
    Column('goods_weight', Integer, comment="所有的数都乘1000"),
)

default_birthday_string = "2023-10-01 12:17:00"
default_birthday = datetime.strptime(default_birthday_string, "%Y-%m-%d %H:%M:%S")

session_factory = sessionmaker(bind=db_engine, autoflush=False, autocommit=False)
session_factory = scoped_session(session_factory)

def get_session() -> Session:
    """
    获取数据库连接
    """
    return session_factory()


db_session = lambda: get_session()


"""
获取产品数据
"""
goods_data = get_product()
if goods_data:
    print(goods_data)
    insert_goods = goods.insert()
    result = db_session().execute(insert_goods, goods_data)
    db_session().commit()

db_session().close()
print("创建完成")